﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Threading;
using System.IO;
using hedefgrup.reports.renault.date;
using System.Web;
using System.Xml;
using hedefgrup.reports.renault.database;
using System.Data;
using System.Globalization;

namespace hedefgrup.reports.common
{
    public class Report_VerbatimByCodePersonel : Report_Abstract
    {
        private const string excelXmlTemplate =
@"<?xml version=""1.0""?>
<?mso-application progid=""Excel.Sheet""?>
<ss:Workbook xmlns:o=""urn:schemas-microsoft-com:office:office"" xmlns:x=""urn:schemas-microsoft-com:office:excel"" xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet"">
  <Styles xmlns=""urn:schemas-microsoft-com:office:spreadsheet"">
  <Style ss:ID=""Default"" ss:Name=""Normal"">
   <Alignment ss:Vertical=""Bottom""/>
   <Borders/>
   <Font ss:FontName=""Calibri"" x:CharSet=""162"" x:Family=""Swiss"" ss:Size=""11""
    ss:Color=""#000000""/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID=""s62"" ss:Name=""Hyperlink"">
  </Style>
  <Style ss:ID=""s65"" ss:Parent=""s62"">
   <Font ss:FontName=""Calibri"" x:CharSet=""162"" x:Family=""Swiss"" ss:Size=""11""
    ss:Color=""#000000"" ss:Underline=""Single""/>
  </Style>
 </Styles>
</ss:Workbook>";

        public static string OutputPath = Path.Combine(Path.GetTempPath(), "Hedef-Grup\\Reports\\Report_VerbatimByCode");
        private const string styleSheetUri = "urn:schemas-microsoft-com:office:spreadsheet";

        public Report_VerbatimByCodePersonel()
            : base()
        {
            base.Criterias.Remove(GetCriteriaByName("Network"));
            base.Criterias.Remove(GetCriteriaByName("Organization"));
        }

        protected override void SetCriteriaList()
        {
            Criterias.Add(new Criteria_BeginMonth() { Context = Context });
            Criterias.Add(new Criteria_EndMonth() { Context = Context });
            Criterias.Add(new Criteria_Network() { Context = Context });
            Criterias.Add(new Criteria_Organization() { Context = Context });
        }

        public override string DisplayName
        {
            get { return "Personel ve Hizmet Raporu"; }
        }

        public override bool Authorized
        {
            get
            {
                string userName = Context.User.getUserName().ToLowerInvariant();
                return base.Authorized &&
                (
                    Context.User.getIsAdministrator() ||
                    userName == "nihan.damli" ||
                    userName == "metin.dinler" ||
                    userName == "cengiz.tozlu" ||
                    userName == "suha.akbas" ||
                    userName == "onur.nar"
                );
            }
        }

        public override bool SupportsProject(string projectName)
        {
            return projectName == Global.PROJECT_SATIS_SUREC;
        }

        public override string SortPrefix
        {
            get { return "0xFF1"; }
        }

        public override ReportResult GenerateReport()
        {
            ReportResult results = new ReportResult(this);
            Row headerRow = new Row();
            results.HeaderRows.Add(headerRow);

            headerRow.Cells.Add(new Cell() { Text = "Rapor Dosyası<input type='hidden' name='prepare_new_verbatim_by_code_report' id='prepare_new_verbatim_by_code_report'value='0' />" });
            headerRow.Cells.Add(new Cell() { Text = "Talep Tarihi" });
            headerRow.Cells.Add(new Cell() { Text = "Durumu" });

            Directory.CreateDirectory(OutputPath);

            if (HttpContext.Current.Request["prepare_new_verbatim_by_code_report"] == "1")
            {
                string beginYearAndMonthOfReport = GetCriteriaByName("Begin Month").Values[0];
                string endYearAndMonthOfReport = GetCriteriaByName("End Month").Values[0];
                string beginMonthOfReport = new reportMonth(beginYearAndMonthOfReport).getAsDateTime().ToString("MMMMyyyy", CultureInfo.GetCultureInfo("en-US"));
                string endMonthOfReport = new reportMonth(endYearAndMonthOfReport).getAsDateTime().ToString("MMMMyyyy", CultureInfo.GetCultureInfo("en-US"));
                string reportFileName = Path.Combine(OutputPath, string.Format("{0:yyyy-MM-dd-HH-mm-ss}_{1}_{2}_Fabrika_Raporu.xls", DateTime.Now, beginMonthOfReport, endMonthOfReport));

                new Thread(new ParameterizedThreadStart(GenerateReportInBackground)).Start(reportFileName);

                Thread.Sleep(1000);
            }

            foreach (string reportFile in Directory.GetFiles(OutputPath, "*.xls"))
            {
                bool isInProgress = File.Exists(reportFile + ".running");
                bool isError = File.Exists(reportFile + ".error");

                Row reportRow = new Row();
                reportRow.Cells.Add(new Cell() { Text = Path.GetFileName(reportFile) });
                reportRow.Cells.Add(new Cell() { Text = DateTime.ParseExact(Path.GetFileName(reportFile).Split('_')[0], "yyyy-MM-dd-HH-mm-ss", null).ToString("dd MMMM yyyy dddd HH:mm:ss") });
                reportRow.Cells.Add(new Cell() { Text = isError ? "Hata Almış" : isInProgress ? "Oluşturuluyor" : "<a target='_blank' href='/dl.aspx?" + Path.GetFileName(reportFile) + "'>Hazır (İndir)</a>" });

                results.Rows.Add(reportRow);
            }

            Row newReportRow = new Row();
            newReportRow.Cells.Add(new Cell() { Text = "<a href=\"javascript:document.getElementById('prepare_new_verbatim_by_code_report').value = '1';document.forms[0].submit();\">Yeni Rapor Oluştur</a>", Span = 3 });
            results.Rows.Add(newReportRow);

            return results;
        }

        private void GenerateReportInBackground(object reportFileNameBoxed)
        {
            string reportFileName = (string)reportFileNameBoxed;

            using (File.Create(reportFileName)) { }
            using (File.Create(reportFileName + ".running")) { };

            try
            {
                GenerateReportContent(reportFileName);
            }
            catch (Exception reportGenerationError)
            {
                File.WriteAllText(reportFileName + ".error", reportGenerationError.ToString());
            }
            finally
            {
                File.Delete(reportFileName + ".running");
            }
        }

        private void GenerateReportContent(string reportFileName)
        {
            reportMonth beginMonthToReport = new reportMonth(GetCriteriaByName("Begin Month").Values[0]);
            reportMonth endMonthToReport = new reportMonth(GetCriteriaByName("End Month").Values[0]);

            DataTable dataToReport = db.query("SELECT AY, YIL, IID, MODEL, S3c FROM " + Context.TableName + " (NOLOCK) WHERE AY + YIL * 12 BETWEEN @p_0 AND @p_1 AND S3c IS NOT NULL", beginMonthToReport.getMonthYearValue(), endMonthToReport.getMonthYearValue());
            DataTable verbatimsToReport = db.query("SELECT AY, YIL, IID, Content FROM renault_verbatim (NOLOCK) WHERE Question = 'S3c' AND AY + YIL * 12 BETWEEN @p_0 AND @p_1 AND Project = @p_2", beginMonthToReport.getMonthYearValue(), endMonthToReport.getMonthYearValue(), Context.CompatibleProjectName);

            XmlDocument excelReport = new XmlDocument();
            excelReport.LoadXml(excelXmlTemplate);

            XmlNode productWorksheet = excelReport.CreateElement("Worksheet", styleSheetUri);
            excelReport.DocumentElement.AppendChild(productWorksheet);
            productWorksheet.Attributes.Append(excelReport.CreateAttribute("Name", styleSheetUri)).Value = "Ürün Kodları";
            XmlNode productTable = excelReport.CreateElement("Table", styleSheetUri);
            XmlNode productColumn2 = excelReport.CreateElement("Column", styleSheetUri);
            productColumn2.Attributes.Append(excelReport.CreateAttribute("Index", styleSheetUri)).Value = "2";
            productColumn2.Attributes.Append(excelReport.CreateAttribute("Width", styleSheetUri)).Value = "400";
            productTable.AppendChild(productColumn2);
            productWorksheet.AppendChild(productTable);


            foreach (Tree s3cOption in Context.Questionnaire.GetChild("S3c").Children)
            {
                if (!s3cOption.Text.StartsWith("ÜRÜN") && !s3cOption.Text.StartsWith("ARIZA"))
                {
                    XmlNode linkRow = excelReport.CreateElement("Row", styleSheetUri);

                        productTable.AppendChild(linkRow);
                    
                    AppendCell(excelReport, linkRow, null, null, s3cOption.Key, "#'" + s3cOption.Key + "'!A1");
                    AppendCell(excelReport, linkRow, null, null, s3cOption.Text, "#'" + s3cOption.Key + "'!A1");


                    XmlNode workSheet = excelReport.CreateElement("Worksheet", styleSheetUri);
                    excelReport.DocumentElement.AppendChild(workSheet);

                    workSheet.Attributes.Append(excelReport.CreateAttribute("Name", styleSheetUri)).Value = s3cOption.Key;

                    XmlNode table = excelReport.CreateElement("Table", styleSheetUri);
                    workSheet.AppendChild(table);

                    DataRow[] answerRows = dataToReport.Select("S3c LIKE '%" + s3cOption.Key + "%'");

                    XmlNode optionRow = excelReport.CreateElement("Row", styleSheetUri);
                    table.AppendChild(optionRow);
                    AppendCell(excelReport, optionRow, null, null, s3cOption.Key);
                    AppendCell(excelReport, optionRow, null, null, s3cOption.Text);

                    XmlNode blankRow = excelReport.CreateElement("Row", styleSheetUri);
                    table.AppendChild(blankRow);

                    XmlNode headerRow = excelReport.CreateElement("Row", styleSheetUri);
                    table.AppendChild(headerRow);
                    AppendCell(excelReport, headerRow, null, null, "IID");
                    AppendCell(excelReport, headerRow, null, null, "MODEL");
                    AppendCell(excelReport, headerRow, null, null, "YORUM");


                    AppendCell(excelReport, linkRow, null, null, answerRows.Length.ToString(), "#'" + answerRows.Length.ToString() + "'!A1");

                    foreach (DataRow answerRow in answerRows)
                    {
                        XmlNode row = excelReport.CreateElement("Row", styleSheetUri);
                        table.AppendChild(row);

                        AppendCell(excelReport, row, answerRow, "IID", null);
                        AppendCell(excelReport, row, answerRow, "MODEL", null);

                        DataRow[] verbatimToReport = verbatimsToReport.Select("IID = '" + answerRow["IID"].ToString() + "' AND AY = " + answerRow["AY"].ToString() + " AND YIL = " + answerRow["YIL"].ToString());
                        if (verbatimToReport.Length > 0)
                        {
                            AppendCell(excelReport, row, answerRow, null, verbatimToReport[0]["Content"].ToString());
                        }
                        else
                        {
                            AppendCell(excelReport, row, answerRow, null, "-");
                        }
                    }
                }
            }

            excelReport.Save(reportFileName);
        }

        private static void AppendCell(XmlDocument excelReport, XmlNode row, DataRow dataRow, string columnName, string value)
        {
            AppendCell(excelReport, row, dataRow, columnName, value, null);
        }

        private static void AppendCell(XmlDocument excelReport, XmlNode row, DataRow dataRow, string columnName, string value, string linkString)
        {
            XmlNode cell = excelReport.CreateElement("Cell", styleSheetUri);
            row.AppendChild(cell);

            if (linkString != null)
            {
                // <Cell ss:StyleID="s62" ss:HRef="#'1009'!A1"><Data ss:Type="String">bla bla bla</Data></Cell>
                cell.Attributes.Append(excelReport.CreateAttribute("HRef", styleSheetUri)).Value = linkString;
                cell.Attributes.Append(excelReport.CreateAttribute("Style" + "id".ToUpperInvariant(), styleSheetUri)).Value = "s65";
            }

            XmlNode data = excelReport.CreateElement("Data", styleSheetUri);
            cell.AppendChild(data);
            XmlAttribute dataType = excelReport.CreateAttribute("Type", styleSheetUri);
            data.Attributes.Append(dataType);

            dataType.Value = "String";

            if (value == null)
            {
                data.InnerText = dataRow[columnName].ToString();
            }
            else
            {
                data.InnerText = value;
            }
        }
    }
}
